﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Diagnostics;
using System.Data.SQLite;
using System.IO;

namespace MobileManagement.Library
{
    public class MobileManagement
    {
        #region Static Fields and Methods
        private static MobileManagement _Instance = null;

        public static MobileManagement Instance()
        {
            if (MobileManagement._Instance == null)
            {
                MobileManagement._Instance = new MobileManagement();
            }

            if (File.Exists("MobileManagement.sqlite") == false)
            {
                Debug.WriteLine("Instance -> MobileManagement.sqlite doesn't exist, creating...");
                SQLiteConnection.CreateFile("MobileManagement.sqlite");
                Debug.WriteLine("Instance -> MobileManagement.sqlite created OK!");
                Debug.WriteLine("Instance -> Adding table Mobiles...");
                MobileManagement._Instance.ExecuteQuery("Create Table MobileHistory (mobileId bigint, lastUpdate TEXT)");
                Debug.WriteLine("Instance -> Mobiles table added OK!");
            }
            else
            {
                Debug.WriteLine("Instance -> Looking for mobiles...");
                MobileManagement._Instance._Mobiles = MobileManagement._Instance.ExecuteQuery("SELECT mobileId FROM MobileHistory ");
                Debug.WriteLine("Instance -> " + MobileManagement._Instance._Mobiles.Count + " found!");
            }

            return MobileManagement._Instance;
        }
        #endregion

        private Thread _MainThread = null;
        //private int _SleepingTime = 7200;
        private int _SleepingTime = 1;
        private SQLiteConnection _SQLiteConn;
        private SQLiteCommand _SQLiteCom;
        private List<string> _Mobiles;
        public delegate void MobileNotUpdatingHandler(Mobile m, EventArgs e);
        public event MobileNotUpdatingHandler MobileNotActive;

        private MobileManagement()
        {
           
        }

        private List<string> ExecuteQuery(string txtQuery)
        {
            List<string> results = new List<string>();

            using (_SQLiteConn = new SQLiteConnection("Data Source=MobileManagement.sqlite;Version=3;New=False;"))
            {
                _SQLiteConn.Open();
                using (_SQLiteCom = _SQLiteConn.CreateCommand())
                {
                    _SQLiteCom.CommandText = txtQuery;
                    Debug.WriteLine("ExecuteQuery -> Executing ->" + _SQLiteCom.CommandText);
                    using (SQLiteDataReader reader = _SQLiteCom.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string result = String.Empty;

                                for (int i = 0; i < reader.VisibleFieldCount; i++)
                                {
                                    result += reader[i].ToString() + "|";
                                }

                                result = result.Substring(0, result.Length - 1);
                                results.Add(result);
                            }
                        }
                    }
                }
                _SQLiteConn.Close();
            }

            return results;
        }

        public void StartWork()
        {
            Debug.WriteLine("StartWork -> Starting..");
            this._MainThread = new Thread(() => this.Work());
            this._MainThread.Start();
            Debug.WriteLine("StartWork -> Started OK");
        }

        public void StopWork()
        {
            if (this._MainThread == null)
            {
                throw new ApplicationException("Falta inicializar el thread de trabajo. Primero llame a StartWork()");
            }
            this._MainThread.Abort();
            Debug.WriteLine("StopWork -> Aborted OK");
        }

        public bool UpdateMobile(string mobileId, DateTime updateTime)
        {
            //this.ExecuteQuery(" MERGE MobileHistory AS target "+
            //                  " USING (SELECT "+mobileId+", '"+updateTime.ToString("yyyyMMdd HH:mm:ss")+"') AS source (mobileId, lastUpdate) "+
            //                  " ON (target.mobileId = source.mobileId) "+
            //                  "  WHEN MATCHED THEN  "+
            //                  "      UPDATE SET lastUpdate = source.lastUpdate "+
            //                  " WHEN NOT MATCHED THEN	"+
            //                  "    INSERT (mobileId, lastUpdate) " +
            //                  "     VALUES (source.mobileId, source.lastUpdate) ");

            this.ExecuteQuery(
                              " UPDATE MobileHistory SET lastUpdate = '" + updateTime.ToString("yyyyMMdd HH:mm:ss") + "' " +
                              " WHERE mobileId = " + mobileId
                              );
            return true;
        }

        public bool ExistMobile(string mobileId)
        {
            return (this.ExecuteQuery(
                              " SELECT mobileId FROM MobileHistory " +
                              " WHERE mobileId = " + mobileId
                              )).Count > 0;
        }

        public bool InsertMobile(string mobileId)
        {
            this.ExecuteQuery(" INSERT INTO MobileHistory(mobileId, lastUpdate) VALUES( " + mobileId + ",'" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "')");
            return true;
        }

        public bool DeleteMobile(string mobileId)
        {           
            this.ExecuteQuery(" DELETE FROM MobileHistory WHERE mobileId =  " + mobileId);
            return true;
        }

        public bool DropTable()
        {
            try
            {
                this.ExecuteQuery(" DROP TABLE MobileHistory");
            }
            catch (SQLiteException ex)
            {
                throw new Exception("[DropTable] SQL EXCEPTION :"+ ex);

            }
            catch (Exception ex)
            {
                throw new Exception("[DropTable] EXCEPTION :" + ex);
            }


            return true;
        }

        private void Work()
        {
            Debug.WriteLine("Work -> Started OK");

            for (int i = 0; i < _SleepingTime; i++)         //CADA cuanto Revisa
            {
                Thread.Sleep(1000);
            }


            while (true)
            {
                try
                {
                    foreach (string mobile in _Mobiles)
                    {
                        DateTime lastUpdate = DateTime.Now;
                        Debug.WriteLine("Work -> Checking mobile "+mobile+" ...");
                        string strLastUpdate = (this.ExecuteQuery("SELECT lastUpdate FROM MobileHistory where mobileId = " + mobile ))[0];

                        Debug.WriteLine("Work -> Last update was on " + strLastUpdate + " ...");

                        lastUpdate = new DateTime(Convert.ToInt32(strLastUpdate.Substring(0, 4)),
                                                  Convert.ToInt32(strLastUpdate.Substring(4, 2)),
                                                  Convert.ToInt32(strLastUpdate.Substring(6, 2)),
                                                  Convert.ToInt32(strLastUpdate.Substring(9, 2)),
                                                  Convert.ToInt32(strLastUpdate.Substring(12, 2)),
                                                  Convert.ToInt32(strLastUpdate.Substring(15, 2)));
                        
                        

                        Mobile mob = new Mobile(mobile, lastUpdate);

                        if (mob.IsActive == false)
                        {

                            if (MobileNotActive != null)
                            {
                                Debug.WriteLine("Work -> Alerting " + mob.ToString() + " ...");
                                MobileNotActive(mob, null);
                            }
                        }
                        else Debug.WriteLine("Work -> Mobile " + mobile + " is OK. Diff is " + mob.ToString());
                                                
                        Thread.Sleep(1000);
                        
                    }

                    Debug.WriteLine("Work -> Working...");
                    
                }
                catch (ThreadAbortException threadAbortEx)
                {
                    Debug.WriteLine("Work -> Aborting... -> " + threadAbortEx.Message);
                    break;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("Work -> Exception -> " + ex.Message);
                    Debug.WriteLine("Work -> SS -> " + ex.StackTrace);
                }

                for (int i = 0; i < _SleepingTime; i++)         //CADA cuanto Revisa
                {
                    Thread.Sleep(1000);
                }
            }

            Debug.WriteLine("Work -> Aborted Ok!");
        }

    }
}
